/*
 * PrintFormatDAO.java
 *
 * Created on April 6, 2007, 11:23 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.jjenkins.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
/**
 * This method has the standard CRUD methods which enables to interact with database and fetch the result
 * @author uni
 */
public class PrintFormatDAO {
    
    private static PrintFormatDAO instance = null;
    private PreparedStatement readUnitPrice = null;
    private PreparedStatement insertPrintFormat = null;
    private PreparedStatement readPrintFormat = null;
    private PreparedStatement updatePrintFormat = null;
    
    
    /** Creates a new instance of PrintFormatDAO */
    private PrintFormatDAO() {
    }
    
    /**
     * This method implements the singleton pattern. It insures that only
     * on instance of PrintFOrmatexists
     * @return BackupDAO
     */
    
    public static synchronized PrintFormatDAO getInstance() {
        if (instance == null) {
            instance = new PrintFormatDAO();
        }
        return instance;
    }//end method
    
       /**
     * This method creates a new RevenueSourceBO with a unique QUID, puts the PrintFormat
     * in cache, and then returns it to the calling method.
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     * @return PrintFormat
     */
    public PrintFormat createPrintFormat(String paper_type, String size, String format, double unit_price)throws Exception{
        PrintFormat p = createPrintFormat(GUID.generate(),paper_type,size,format,unit_price);
        Cache.getInstance().put(p.getId (),p);
        return p;
    }
    
    /**
     * create print format
     */
    public PrintFormat createPrintFormat(String guid, String paper_type, String size, String format, double unit_price) throws Exception{
        PrintFormat p = new PrintFormat(guid);
        p.setPaperType (paper_type);
        p.setSize (size);
        p.setFormat (format);
        p.setUnitPrice (unit_price);
        Cache.getInstance().put(p.getId (),p);
        return p;
    }

    /**
     * insert print format
     */
    public void insertPrintFormat(PrintFormat p)throws Exception{
        Connection conn = ConnectionPool.getInstance().get();
        this.insertPrintFormat(p,conn);
    }
    /**
     * insert print format
     */
    public void insertPrintFormat(PrintFormat p , Connection conn)throws Exception{
        try {
            this.insertPrintFormat = conn.prepareStatement("insert into print_format values" +
                    "(?,?,?,?,?)");
            insertPrintFormat.setString(1,p.getId ());
            insertPrintFormat.setString(2,p.getPaperType ());
            insertPrintFormat.setString(3,p.getSize ());
            insertPrintFormat.setString(4,p.getFormat ());
            insertPrintFormat.setDouble (5,p.getUnitPrice ());
            insertPrintFormat.execute();
            ConnectionPool.getInstance().release(conn);
        } catch (Exception ex) {
            ex.printStackTrace();
            conn.rollback();
            ConnectionPool.getInstance().release(conn);
            throw ex;
        }
    }
    
    /**
     * update print format
     */
    public void updatePrintFormat(PrintFormat f)throws Exception{
        Connection conn = ConnectionPool.getInstance().get();
        updatePrintFormat(f,conn);
     }
    /**
     * update print format
     */
    public void updatePrintFormat(PrintFormat f, Connection conn)throws Exception{
        try {
            this.updatePrintFormat = conn.prepareStatement("update print_format SET paper_type=?, size=?, format=?, unit_price=? WHERE print_format_guid=?");
            updatePrintFormat.setString(1,f.getPaperType ());
            updatePrintFormat.setString(2,f.getSize ());
            updatePrintFormat.setString(3,f.getFormat ());
            updatePrintFormat.setDouble(4,f.getUnitPrice ());
            updatePrintFormat.setString(5,f.getId ());
            updatePrintFormat.executeUpdate();
            ConnectionPool.getInstance().release(conn);
        } catch (Exception ex) {
            ex.printStackTrace();
            conn.rollback();
            ConnectionPool.getInstance().release(conn);
            throw ex;
        }         
    }
    
    
    
   /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     * @param id id of PrintFormat
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     * @return BackupBO
     */
    public PrintFormat readPrintFormat(String guid)throws Exception{
        PrintFormat p = null;
        Connection conn = ConnectionPool.getInstance().get();
        p = readPrintFormat(guid,conn);
        return p;
    }
    
   /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * @param id id of PrintFormat
     * @param conn connection
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     * @return PrintFormat
     */
    public PrintFormat readPrintFormat(String guid, Connection conn)throws Exception{
        PrintFormat p = null;
        if(Cache.getInstance().containsKey(guid)){
            p = (PrintFormat)Cache.getInstance().get(guid);
            Cache.getInstance().touch(guid);
            
        }else{
            try {
                
                this.readPrintFormat = conn.prepareStatement("select * from print_format where print_format_guid = ?");
                this.readPrintFormat.setString(1,guid);
                ResultSet rs = this.readPrintFormat.executeQuery();
                while(rs.next()){
                    p = this.createPrintFormat(rs.getString("print_format_guid"),
                            rs.getString("paper_type"),
                            rs.getString("size"),
                            rs.getString("format"),
                            Double.parseDouble(rs.getString("unit_price")));
                    
                }
                ConnectionPool.getInstance().release(conn);
            } catch (Exception ex) {
                ex.printStackTrace();
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
                throw ex;
            }
            
        }
        return p;
    }

    
    
    /**
     * get unit price
     */
    public PrintFormat getUnitPrice(String paper_type, String size)throws Exception{
        Connection conn = ConnectionPool.getInstance().get();
        PrintFormat p = getUnitPrice(paper_type, size, conn);
        return p;
    }
    
    /**
     * get unit price
     */
    public PrintFormat getUnitPrice(String paper_type, String size, Connection conn)throws Exception{
        PrintFormat p = null;
        try {
            this.readUnitPrice = conn.prepareStatement("select * from print_format " +
                    "where paper_type = ? and size = ?");
            this.readUnitPrice.setString(1,paper_type);
            this.readUnitPrice.setString(2,size);
            ResultSet rs = this.readUnitPrice.executeQuery();            
            while(rs.next()){
                p = this.createPrintFormat(rs.getString("print_format_guid"),
                        rs.getString("paper_type"),
                        rs.getString("size"),
                        //rs.getString("format"),
                        Double.parseDouble(rs.getString("unit_price")));
                
                    }
          
            ConnectionPool.getInstance().release(conn);
        } catch (NumberFormatException ex) {
            ex.printStackTrace();
            conn.rollback();
            ConnectionPool.getInstance().release(conn);
            throw ex;
        }
        return p;
    }
    
     
    /**
     * This method gets a list of all the stores.
     * @return List<List>
     */
    public List<List> getAll() throws DataException, SQLException {
        // get the connection
        Connection conn = null;
        List<List> list = new ArrayList();
        
        try{
            conn = ConnectionPool.getInstance().get();
            
            // sql the names, phone, and ids
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT DISTINCT(*) FROM print_format");
            
            // while loop to populate the list from the results
            while(rs.next()) {
                List print_format = new ArrayList();
                print_format.add(rs.getString("print_format_guid"));
                print_format.add (rs.getString ("paper_type"));
                print_format.add (rs.getString ("size"));
                print_format.add (rs.getString ("format"));
                print_format.add (rs.getDouble ("unit_price"));
                list.add (print_format);
            }
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
        }finally{
            // release the connection
            try{
                ConnectionPool.getInstance().release(conn);
            }catch (ConnectionPoolException e){
                throw new DataException("Could not disconnect.");
            }
        }
        // return the list
        return list;
    }
    
    

    
}
